﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Hospital.Common
{
    public class SQLHelper
    {
        //获取数据库连接字符串
        //static string str = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection_string"].ConnectionString;
        static string connectionString = System.Configuration.ConfigurationManager.AppSettings["DBConnection_string"];
        //private string connectionString;
        //创建连接对象
        SqlConnection con = null;
        //创建命令对象
        SqlCommand cmd = null;
        //创建连接对象
        SqlDataAdapter da = null;
        DataSet ds = null;
        //创建一个表对象
        DataTable dt = null;

        //public string ConnectionString
        //{
        //    set { connectionString = value; }
        //    get { return connectionString; }
        //}

        #region 构造函数
        public SQLHelper()
        {
            try
            {
                con = new SqlConnection(connectionString);
            }
            catch
            {

            }
        }
        #endregion

        #region 具体数据查询的方法
        /// <summary>
        /// 调用方法查询一个或多个表的信息，并返回DataSet类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteDataSet(string sql, bool re, params object[] values)
        {
            ds = new DataSet();
            da = new SqlDataAdapter(sql, con);

            if (re)
            {
                //定义命令类型
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
            }//if (re)//判断命令类型

            if ((values != null) && (values.Length > 0))
            {
                //添加参数
                da.SelectCommand.Parameters.AddRange(values);
            }//if (values != null)//判断参数是否存在

            try
            {
                da.Fill(ds);

            }//try
            catch (SqlException ex)
            {

            }//try catch(SqlException ex)捕获数据库异常
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)关闭连接
            }//try catch(SqlException ex) finally

            return ds;

        }//public DataSet GetDataSet(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法查询单个表的信息，并返回DataTable类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回DataTable</returns>
        public DataTable ExecuteDataTable(string sql, bool re, params object[] values)
        {
            dt = new DataTable();
            da = new SqlDataAdapter(sql, con);

            if (re)
            {
                //定义命令类型
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
            }//if (re)

            if (values != null)
            {
                //添加参数
                da.SelectCommand.Parameters.AddRange(values);
            }//if (values != null)

            try
            {
                da.Fill(dt);
            }//try
            catch (SqlException ex)
            {
            }//try catch(SqlException ex)
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)

            }//try catch(SqlException ex) finally

            return dt;

        }//public DataTable GetDataTable(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法查询单个值，返回object类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回object</returns>
        public object ExecuteValue(string sql, bool re, params object[] values)
        {
            object strReturn = null;
            try
            {
                cmd = new SqlCommand(sql, con);
                if (re)
                {
                    //定义命令类型
                    cmd.CommandType = CommandType.StoredProcedure;
                }//if (re)

                if (values != null)
                {
                    //添加参数
                    cmd.Parameters.AddRange(values);
                }//if (values != null)


                if (con.State == ConnectionState.Closed)
                {
                    //打开连接
                    con.Open();
                }//if(con.State == ConnectionState.Closed)
                //读取一行一列的数据
                strReturn = cmd.ExecuteScalar();
                //如果返回值是数据库空值类型
                if (strReturn == DBNull.Value)
                {
                    strReturn = null;
                }
            }//try
            catch (SqlException ex)
            {
            }//try catch(SqlException ex)
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)

            }//try catch(SqlException ex) finally

            return strReturn;

        }//public object GetValue(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法更新数据库，并返回受影响行数
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回受影响行数</returns>
        public int ExecuteCommand(string sql, bool re, params object[] values)
        {
            int count = 0;
            cmd = new SqlCommand(sql, con);

            if (re)
            {
                //定义命令类型
                cmd.CommandType = CommandType.StoredProcedure;
            }//if(re)

            if (values != null)
            {
                //向命令对象的参数集合里添加参数
                cmd.Parameters.AddRange(values);
            }//if(values != null)

            try
            {

                if (con.State == ConnectionState.Closed)
                {
                    //打开连接
                    con.Open();
                }//if(con.State == ConnectionState.Closed)

                count = cmd.ExecuteNonQuery();//  执行命令，返回受影响行数
            }//try
            catch (SqlException ex)
            {
            }//try catch(SqlException ex)
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)

            }//try catch(SqlException ex) finally

            return count;

        }//public int ProExecuteCommand(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 获取参数的数组
        /// </summary>
        /// <param name="size"></param>
        /// <returns></returns>
        public object[] GetParameters(int size)
        {
            return new System.Data.SqlClient.SqlParameter[size];
        }

        /// <summary>
        /// 获取某个参数
        /// </summary>
        /// <returns></returns>
        public object GetParameter()
        {
            return new System.Data.SqlClient.SqlParameter();
        }

        #endregion
    }
}
